How to: Use a query to create new table from data in existing table(s).
Solution:
Create a new query in Design view, select the 'Query' menu and select 'Make Table...', type a name, and choose 'OK'. Then, to make the new table, run the query.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Click 'New'. (The New Query dialog box appears.)
4) Select 'Design View' from the list box.
5) Click 'OK'. (The query opens in Design view, and the Show Table dialog box appears.)
6) Do one of the following:
a) Click the 'Tables' tab to display a list of tables only.
b) Click the 'Queries' tab to display a list of queries only.
c) Click the 'Both' tab to display a list of both tables and queries.
7) Select the table(s) and/or querie(s) to include in the query from the list box.
NOTE: To select more than one adjacent table or query, press and hold down SHIFT while selecting the tables or queries. To select more than one non-adjacent table or query, press and hold down CTRL while selecting the tables or queries.
8) Click 'Add'.
9) Click 'Close' when all of the desired tables and/or queries have been added.
10) Add fields to the query:
a) Select the desired field from a table field list in the top half of the Query Design window.
b) Drag the field to the desired column of the design grid in the bottom half of the Query Design window.
Design grid
c) Release the mouse button. (The field name appears in the Field row, and its table appears in the Table row.)
Field row and Table row
d) Repeat steps10)a) through 10)c) for each field to add to the query.
NOTE: Fields can also be added to the query by selecting a field from the drop-down list box in the 'Field' row of the design grid in the bottom half of the Query Design window.
Design grid
11) (Optional) If desired, specify criteria in the 'Criteria' and 'or' rows for the fields.
12) Select the 'Query' menu and select 'Make Table...'. (The Make Table dialog box appears.)
13) In the 'Table Name' box, type a name for the new table that will be created from data from the tables selected in step 7).
14) Do one of the following:
a) To create the new table in the open database, select the 'Current Database' radio button.
b) To create the new table in a different database:
1] Select the 'Another Database' radio button.
2] Type the path and filename of the other database in the 'File Name' box.
15) Click 'OK'. (The Make Table dialog box closes.)
16) To preview the new table before creating it, select the 'View' menu and select 'Datasheet'.
17) To create the new table:
a) Make sure Query Design view is open.
NOTE: If Datasheet view is open, select the 'View' menu and select 'Query Design'.
b) Select the 'Query' menu and select 'Run'. (The table is created and is added to the Database window list of tables.)
18) To save the query:
a) Select the 'File' menu and select 'Save'. (The Save As dialog box appears.)
b) Type a name for the make-table query in the 'Query Name' box.
c) Click 'OK'.
19) Select the 'File' menu and select 'Close' to close the Query Design view.
